package com.example.exp_4_javaweb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentHelper {

    static Connection conn = null;
    static PreparedStatement ps = null;
    static ResultSet rs= null;

    Student student = null;

    public StudentHelper(Student student) {
        this.student = student;
    }
    public StudentHelper(){};

    public void add() {
        try {
            conn = DBConnection.getConnection();
            String sql = "insert into test.student(name,password) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,student.getName());
            ps.setString(2, student.getPassword());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs,ps,conn);
        }
    }
    public void delete(int id) {
        try {
            conn = DBConnection.getConnection();
            String sql = "delete from test.student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs,ps,conn);
        }
    }
    public void update() {
        try {
            conn = DBConnection.getConnection();
            String sql = "update test.student set name = ?,password = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,student.getName());
            ps.setString(2,student.getPassword());
            ps.setInt(3,student.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs,ps,conn);
        }
    }
    public List<Student> list(){
        List<Student> list = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select * from test.student";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                Student student = new Student(id,name,password);
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs,ps,conn);
        }
        return list;
    }
}
